Join Keys
Database tables usually contain one or more key columns. Key columns generally list unique identifiers in order to uniquely identify rows in the database tables. Joins combine rows from multiple table based on related or common columns shared by them; these columns are usually key columns. The value (generally key columns) used to combine rows between tables is called a join key.
Editing Join Keys
Join keys are edited from the Relationship window in the Properties panel of the selected join. The join key table shows the tables being joined (purple arrow below), the join direction (yellow highlight), the join key columns (blue arrow), and the join key operator (red highlight):
Add or Edit Join Keys
To add a new join key, click 'New Join Key' (blue highlight above), and then select the required columns and operator. Join key columns can be changed simply by selecting a different column from the drop-down list.
Multi-Join Keys
You may have some tables containing multiple identical key columns. In this case, you can edit the join to include multiple join keys. In this way, you can join 2 tables by multiple shared key columns, via a single join. The join type and direction for each join key will be the same, but the join key operation can be different.
And/ Or Conditions
Join the given tables based on a condition by inserting an And/ Or function between the columns (green highlight above).
- And: join if both conditions are true.
- Or: join if either condition is true.
Join Direction
Click the double arrows icon to change the direction of the join (yellow highlight above). The table denoted by the 
icon is the table on the 'left' (the table from which the join originates, known as the primary table). The table denoted by the 
icon is the table on the 'right' (the table to which the join is connected, known as the foreign table). The primary table is the table containing the primary key column, which the foreign table contains the foreign key column.
Typically, joins will be one-way (unidirectional), but there may be circumstances in which your want the data between 2 tables to flow in both directions (bidirectional joins). this can be achieved by enabling the 'Bidirectional' option.
- Click here to learn about bidirectional joins.
Join Key Operators
Select the join operator from the drop-down list (red highlight below). By default the join operation is set to = but it can be changed to a non equal operator if required. Equal joins are used to join rows where the primary key columns in each table match. Non equal functions are used to create joins where the key values don't match.
Non equal joins are used for a range of purposes, like checking for duplicate data, generating running totals, or to join according to a range of values or dates. You might want to find the names of salespeople who were hired less than 3 months ago, so their on-boarding and progress can be tracked. Or you might want to find a list of students who are not studying Physics this semester, so you can check if whether or not they have enough credits.
The join operators available are:
- = joins rows where the key in both tables is the same.
- < > joins rows where the key in both tables is not the same.
- > joins rows where the key in the left table is greater than the key in the right table.
- > = joins rows where the key in the left table is greater than or equal to the key in the right table.
- < joins rows where the key in the right table is smaller than the key in the right table.
- < = joins rows where the key in the right table is smaller than or equal to the key in the right table.